<table border=1>
    <tr>
        <th ROWSPAN=2>Fecha</th>
        <th COLSPAN=3>Comprobante de Pago</th>
        <th COLSPAN=3>Proveedor</th>
        <th ROWSPAN=2>Base</th>
        <th ROWSPAN=2>IGV</th>
        <th ROWSPAN=2>Total</th>
        <th ROWSPAN=2>Tipo Cambio</th>
    </tr>
    <tr>
        <th>Tipo Documento</th>
        <th>Serie</th>
        <th>Numero</th>
        <th>Tipo</th>
        <th>RUC</th>
        <th>Razon Social</th>
    </tr>

    <?php
    require_once('PHPExcel.php');
    require_once 'PHPExcel/IOFactory.php';
    require '../connection.php';

    //$inputFileName = '../uploads/01 - Compras.xlsx';
    //$inputFileName = '../uploads/02 - Compras.xlsx';
    //$inputFileName = '../uploads/03 - Compras.xlsx';
    //$inputFileName = '../uploads/04 - Compras.xlsx';
    
    //$inputFileName = '../uploads/01 - Ventas.xlsx';
    //$inputFileName = '../uploads/02 - Ventas.xlsx';
    $inputFileName = '../uploads/03 - Ventas.xlsx';
    /*$inputFileName = '../uploads/04 - Ventas.xlsx';*/

    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
    $cacheSettings = array('memoryCacheSize' => '20MB');
//set php excel settings
    PHPExcel_Settings::setCacheStorageMethod(
            $cacheMethod, $cacheSettings
    );

    $objectReader = PHPExcel_IOFactory::createReader('Excel2007');
    $objectReader->setReadDataOnly(true);

    $objPHPExcel = $objectReader->load($inputFileName);
//$sheet = $objPHPExcel->getSheet(0);
    $objWorksheet = $objPHPExcel->getActiveSheet();
//$arrayLabel = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC");
    /* echo '<table border=1>' . "\n";
      foreach ($objWorksheet->getRowIterator() as $row) {
      echo '<tr>' . "\n";
      $cellIterator = $row->getCellIterator();
      $cellIterator->setIterateOnlyExistingCells(false);
      foreach ($cellIterator as $cell) {
      echo '<td>' . $cell->getValue() . '</td>' . "\n";
      }
      echo '</tr>' . "\n";
      }
      echo '</table>' . "\n"; */
    $arrayLabel = array("C", "E", "F", "H", "I", "J", "K", "L", "M", "U", "Y");
    $starting = 11;
    $end = 110;
    $post = 11;

    $db = getConnection();
    foreach ($objWorksheet->getRowIterator() as $row) {
        if ($row->getRowIndex() > '11') {
            $val = $objWorksheet->getCell("C" . $post)->getValue();
            if ($val != "") {
                echo '<tr>' . "\n";
                //------------------------------------------------------------------
                //fecha  
                $value = floor($val);
                $date = new DateTime('1899-12-30');
                $date->add(new DateInterval('P' . $value . 'D'));
                $fecha = $date->format('Y-m-d');
                echo '<td>' . $fecha . '</td>';
                //------------------------------------------------------------------
                $tipo_documento = $objWorksheet->getCell("E" . $post)->getValue();
                echo '<td>' . $tipo_documento . '</td>' . "\n";
                //------------------------------------------------------------------
                $serie_documento = $objWorksheet->getCell("F" . $post)->getValue();
                echo '<td>' . $serie_documento . '</td>' . "\n";
                //------------------------------------------------------------------
                $numero_documento = $objWorksheet->getCell("H" . $post)->getValue();
                echo '<td>' . $numero_documento . '</td>' . "\n";
                //------------------------------------------------------------------
                $proveedor_tipo = $objWorksheet->getCell("I" . $post)->getValue();
                echo '<td>' . $proveedor_tipo . '</td>' . "\n";
                //------------------------------------------------------------------
                $proveedor_ruc = $objWorksheet->getCell("J" . $post)->getValue();
                echo '<td>' . $proveedor_ruc . '</td>' . "\n";
                //------------------------------------------------------------------
                $proveedor_razon_social = utf8_decode($objWorksheet->getCell("K" . $post)->getValue());
                echo '<td>' . $proveedor_razon_social . '</td>' . "\n";
                //------------------------------------------------------------------
                $base_imponible = $objWorksheet->getCell("L" . $post)->getValue();
                echo '<td>' . $base_imponible . '</td>' . "\n";
                //------------------------------------------------------------------
                $igv = $objWorksheet->getCell("M" . $post)->getValue();
                echo '<td>' . $igv . '</td>' . "\n";
                //------------------------------------------------------------------
                $total = $objWorksheet->getCell("U" . $post)->getValue();
                echo '<td>' . $total . '</td>' . "\n";
                //------------------------------------------------------------------
                $tipo_cambio = $objWorksheet->getCell("Y" . $post)->getValue();
                echo '<td>' . $tipo_cambio . '</td>' . "\n";
                //------------------------------------------------------------------
                echo '</tr>' . "\n";
                //------------------------------------------------------------------
                $proveedor_id = 0;
                $sql_list = "SELECT id FROM import_operators WHERE ruc = :ruc";
                try {
                    $db = getConnection();
                    $stmt = $db->prepare($sql_list);
                    $stmt->bindParam("ruc", $proveedor_ruc);
                    $stmt->execute();
                    $rst = $stmt->fetchObject();
                    if ($rst) {
                        $proveedor_id = $rst->id;
                    }
                } catch (PDOException $e) {
                    echo '{"error11":{"text":' . $e->getMessage() . '}}';
                }
                if (!($proveedor_id > 0)) {
                    $sql_add = "INSERT INTO import_operators(name, ruc) VALUES (:name, :ruc)";
                    try {
                        $stmt = $db->prepare($sql_add);
                        $stmt->bindParam("name", $proveedor_razon_social);
                        $stmt->bindParam("ruc", $proveedor_ruc);
                        $stmt->execute();
                        $proveedor_id = $db->lastInsertId();
                    } catch (PDOException $e) {
                        echo '{"error12":{"text":' . $e->getMessage() . '}}';
                    }
                }
                //------------------------------------------------------------------
                $id_tipo_cambio = 0;
                if ($tipo_cambio != 0) {
                    $sql_list = "SELECT id FROM import_type_change WHERE date = :date";
                    try {
                        $db = getConnection();
                        $stmt = $db->prepare($sql_list);
                        $stmt->bindParam("date", $fecha);
                        $stmt->execute();
                        $rst = $stmt->fetchObject();
                        if ($rst) {
                            $id_tipo_cambio = $rst->id;
                        }
                    } catch (PDOException $e) {
                        echo '{"error21":{"text":' . $e->getMessage() . '}}';
                    }
                    if (!($id_tipo_cambio > 0)) {
                        $sql_add = "INSERT INTO import_type_change(date, value) VALUES (:date, :value)";
                        try {
                            $stmt = $db->prepare($sql_add);
                            $stmt->bindParam("date", $fecha);
                            $stmt->bindParam("value", $tipo_cambio);
                            $stmt->execute();
                            $id_tipo_cambio = $db->lastInsertId();
                        } catch (PDOException $e) {
                            echo '{"error22":{"text":' . $e->getMessage() . '}}';
                        }
                    }
                }
                //------------------------------------------------------------------
                $sql_add = "INSERT INTO import_document(date,document_id,serie,number,subtotal,igv,total,operator_id,type_change,operator_type,type,period,year) 
              VALUES (:date,:document_id,:serie,:number,:subtotal,:igv,:total,:operator_id,:type_change,:operator_type,:type,:period,:year)";

                $type=2;//1 - compras,   2 - ventas
                $period=4;
                $year=2013;
                try {
                    $stmt = $db->prepare($sql_add);
                    $stmt->bindParam("date", $fecha);
                    $stmt->bindParam("document_id", $tipo_documento);
                    $stmt->bindParam("serie", $serie_documento);
                    $stmt->bindParam("number", $numero_documento);
                    $stmt->bindParam("operator_id", $proveedor_id);
                    $stmt->bindParam("subtotal", $base_imponible);
                    $stmt->bindParam("igv", $igv);
                    $stmt->bindParam("total", $total);
                    $stmt->bindParam("type_change", $id_tipo_cambio);
                    $stmt->bindParam("operator_type", $proveedor_tipo);
                    $stmt->bindParam("type", $type);
                    $stmt->bindParam("period", $period);
                    $stmt->bindParam("year", $year);

                    $stmt->execute();
                } catch (PDOException $e) {
                    echo '{"error3":{"text":' . $e->getMessage() . '}}';
                }
            }

            $post++;
            //------------------------------------------------------------------            
        }
    }
    $db = null;

    /* foreach ($objWorksheet->getRowIterator() as $row) {
      if ($row->getRowIndex() > '11') {
      echo '<tr>' . "\n";
      for ($j = 0; $j < count($arrayLabel); $j++) {
      if ($arrayLabel[$j] == "C") {//fecha
      $val = $objWorksheet->getCell("C" . $post)->getValue();
      if ($val == "") {
      break;
      }
      $value = floor($val);
      $date = new DateTime('1899-12-30');
      $date->add(new DateInterval('P' . $value . 'D'));
      $value = $date->format('d/m/Y');
      echo '<td>' . $value . '</td>';
      } elseif ($arrayLabel[$j] == "E") {//tipo documento
      echo '<td>' . $objWorksheet->getCell("E" . $post)->getValue() . '</td>' . "\n";
      } elseif ($arrayLabel[$j] == "F") {//serie
      echo '<td>' . $objWorksheet->getCell("F" . $post)->getValue() . '</td>' . "\n";
      } elseif ($arrayLabel[$j] == "H") {//Numero
      echo '<td>' . $objWorksheet->getCell("H" . $post)->getValue() . '</td>' . "\n";
      } elseif ($arrayLabel[$j] == "I") {//tipo proveedor
      echo '<td>' . $objWorksheet->getCell("I" . $post)->getValue() . '</td>' . "\n";
      } elseif ($arrayLabel[$j] == "J") {//ruc
      echo '<td>' . $objWorksheet->getCell("J" . $post)->getValue() . '</td>' . "\n";
      } elseif ($arrayLabel[$j] == "K") {//razon social
      echo '<td>' . $objWorksheet->getCell("K" . $post)->getValue() . '</td>' . "\n";
      } elseif ($arrayLabel[$j] == "L") {//base imponible
      echo '<td>' . $objWorksheet->getCell("L" . $post)->getValue() . '</td>' . "\n";
      } elseif ($arrayLabel[$j] == "M") {//igv
      echo '<td>' . $objWorksheet->getCell("M" . $post)->getValue() . '</td>' . "\n";
      } elseif ($arrayLabel[$j] == "U") {//total
      echo '<td>' . $objWorksheet->getCell("U" . $post)->getValue() . '</td>' . "\n";
      } elseif ($arrayLabel[$j] == "Y") {//tipo de cambio
      echo '<td>' . $objWorksheet->getCell("Y" . $post)->getValue() . '</td>' . "\n";
      } else {//Numero
      echo '<td>error</td>' . "\n";
      }
      }
      echo '</tr>' . "\n";
      $post++;
      }
      } */
    echo '</table>' . "\n";
    ?>
